package com.qc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.qc.common.StringUtil;
import com.qc.dao.UserSecScheduleDao;
import com.qc.entity.SecScheduleEntityVo;
import com.qc.entity.UserSecScheduleEntity;

@SuppressWarnings("rawtypes")
@Repository("userSecScheduleDao")
public class UserSecScheduleDaoImpl implements UserSecScheduleDao{

	@Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
	
	@Override
	public int getListUserSecScheduleCount(String userId,String createTime) {
		
		String sql = "select count(*) from t_secretary_schedule a"
				+ " left join t_user u on a.user_id = u.user_id "
				+ " where 1 = 1 and a.if_del = '0' ";
		
        if (StringUtil.isNotEmpty(userId) && !"undefined".equals(userId) ) {
            sql += " and a.user_id like '%"+userId+"%' ";
        }
        
        if (StringUtil.isNotEmpty(createTime) && !"undefined".equals(createTime) ) {
        	sql += " and a.created_time = '"+createTime+"' ";
        }
        
        return jdbcTemplate.queryForInt(sql);
	}

	
	@Override
	public List getAllUserSecSchedule(String userId,String createTime, int pageNo, int pageSize) {

		int beginRow = (pageNo - 1)* pageSize;
        String sql = " select a.ss_id,"
        		+ " a.user_id,"
        		+ " a.title,"
        		+ " a.shop_province,"
        		+ " a.shop_city,"
        		+ " a.shop_district,"
        		+ " a.shop_address,"
        		+ " a.created_time,"
        		+ " u.nickname"
        		+ " from t_secretary_schedule a "
        		+ " left join t_user u on a.user_id = u.user_id"
        		+ " where 1 = 1 and a.if_del = '0'  ";
        
        if (StringUtil.isNotEmpty(userId) && !"undefined".equals(userId) ) {
            sql += " and a.user_id like '%"+userId+"%' ";
        }
        
        if (StringUtil.isNotEmpty(createTime) && !"undefined".equals(createTime) ) {
        	sql += " and a.created_time = '"+createTime+"' ";
        }
        
        sql += " order by a.created_time DESC ";
        sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
        
        return jdbcTemplate.queryForList(sql);
	}

	@Override
	public int deleteUserSecScheduleId(long id) {
		
		String sql = "update t_secretary_schedule set if_del = '1' where ss_id = " +id;
        return jdbcTemplate.update(sql);
	}

	@Override
	public SecScheduleEntityVo getSecScheduleId(long id) {
		String sql = " select a.title,"
				+ " a.ss_content,"
				+ " a.created_time,"
				+ " a.user_id, "
				+ " u.nickname "
				+ " from t_secretary_schedule a"
				+ " left join t_user u on a.user_id = u.user_id "
				+ " where 1 = 1 and a.if_del = '0' and a.ss_id = " + id;
		
		SecScheduleEntityVo scheduleEntityVo = new SecScheduleEntityVo();
		
		jdbcTemplate.query(sql, new RowMapper<SecScheduleEntityVo>() {
            public SecScheduleEntityVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                scheduleEntityVo.setTitle(rs.getString("title"));
                scheduleEntityVo.setSsContent(rs.getString("ss_content"));
                scheduleEntityVo.setCrateTime(rs.getString("created_time"));
                scheduleEntityVo.setUserName(rs.getString("nickname"));
                scheduleEntityVo.setUserId(rs.getLong("user_id"));
                
            	return scheduleEntityVo;
            }
        });
		
		return scheduleEntityVo;
	}

    @Override
    public int getListSecScheduleCommCount(String name) {
        String sql = "select count(*) from t_secretary_comment a "
            + " left join t_secretary_schedule b on b.ss_id = a.sec_schedule_id "
            + " left join t_user c on c.user_id = a.user_id "
            + " left join t_user u on u.user_id = a.user_id "
            + " where 1 = 1 and a.if_del = '0' ";
        
        if (StringUtil.isNotEmpty(name) && !"undefined".equals(name) ) {
            sql += " and a.uc_content like '%"+name+"%' ";
        }
        
        return jdbcTemplate.queryForInt(sql);
    }

    @Override
    public List getAllSecScheduleComm(String name, int pageNo, int pageSize) {
        int beginRow = (pageNo - 1)* pageSize;
        
        String sql = "select a.sc_id,a.created_time,a.uc_content,"
            + " u.nickname,"
            + " c.nickname as cNickName"
            + " from t_secretary_comment a "
            + " left join t_secretary_schedule b on b.ss_id = a.sec_schedule_id "
            + " left join t_user u on u.user_id =  b.user_id "
            + " left join t_user c on c.user_id = a.user_id"
            + " where 1 = 1 and a.if_del = '0' ";
            
        if (StringUtil.isNotEmpty(name) && !"undefined".equals(name) ) {
            sql += " and a.uc_content like '%"+name+"%' ";
        }
            
        sql += " order by a.created_time DESC ";
        sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
        
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public int deleteSecScheduleCommId(long id) {
        String sql = "update t_secretary_comment set if_del = '1' where sc_id = " +id;
        return jdbcTemplate.update(sql);
    }

    @Override
	public int getMerchantUserCount(String title, long shopId) {
    	String sql = "select count(*)"
    			+ " from t_business_inform b "
    			+ " left join t_secretary_schedule a on b.shop_id = a.shop_id "
    			+ " left join t_user u on u.user_id =  a.user_id "
    			+ " where 1 = 1 and a.if_del = '0' ";
    	
	    	if (StringUtil.isNotEmpty(title) && !"undefined".equals(title) ) {
	            sql += " and title like '%"+title+"%' ";
	        }
	    	
	    	if (shopId != 0) {
	    		sql += " and a.shop_id = '"+shopId+"' ";
	    	}
		return jdbcTemplate.queryForInt(sql);
	}
    
	@SuppressWarnings("unchecked")
	@Override
	public List<UserSecScheduleEntity> getAllMerchantUser(String title, long shopId, int pageNo, int pageSize) {
		
		int beginRow = (pageNo - 1)* pageSize;
    	String sql = "select a.*,b.*,"
    			+ " u.avatar,"
    			+ " u.nickname"
    			+ " from t_secretary_schedule b "
    			+ " left join t_business_inform a on b.shop_id = a.shop_id "
    			+ " left join t_user u on u.user_id =  b.user_id "
    			+ " where 1 = 1 and a.if_del = 0 and b.if_del = 0 ";
    	
	    	if (StringUtil.isNotEmpty(title) && !"undefined".equals(title) ) {
	            sql += " and title like '%"+title+"%' ";
	        }
	    	
	    	if (shopId != 0) {
	    		sql += " and a.shop_id = '"+shopId+"' ";
	    	}
        
	        sql += " order by a.created_time DESC ";
	        sql += " LIMIT "+ String.valueOf(beginRow)+ "," + pageSize;
        
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(UserSecScheduleEntity.class));
	}

}
